﻿using EasyJsonToSql;
using FluentData;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Configuration;

namespace clab
{
    /// <summary>
    /// 数据库辅助类
    /// </summary>
    public class DbHelper
    {
        #region
        public enum DbProviderType
        {
            Mysql,
            Access,
            SqlServer
        }
        public static DbProviderType CurrentDbProviderType { get; private set; }

        public static DbProviderType GetDbProviderType()
        {
            var connection = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"];
            string connectionString = null;
            GetProvider(connection.ProviderName, ref connectionString);
            return CurrentDbProviderType;
        }
        #endregion

        public static IDbContext Db
        {
            get
            {
                IDbContext db = null;
                var connection = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"];
                var timeout = System.Configuration.ConfigurationManager.AppSettings["Timeout"] ?? "300000";
                var connectionString = connection.ConnectionString;

                IDbProvider provider = GetProvider(connection.ProviderName, ref connectionString);
                db = new DbContext().ConnectionString(connectionString, provider).IgnoreIfAutoMapFails(true);
                db.Data.CommandTimeout = Convert.ToInt32(timeout);
                return db;
            }
        }

        private static IDbProvider GetProvider(string ProviderName, ref string connectionString)
        {
            switch (ProviderName)
            {
                case "System.Data.SqlClient":
                    CurrentDbProviderType = DbProviderType.SqlServer;
                    return new SqlServerProvider();
                case "System.Data.OleDb":
                    CurrentDbProviderType = DbProviderType.Access;
                    //connectionString="Provider=Microsoft.Jet.Oledb.4.0;data source="
                    connectionString += HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["AccessDbPath"]);
                    return new AccessProvider();
                case "MySql.Data.MySqlClient":
                default:
                    CurrentDbProviderType = DbProviderType.Mysql;
                    return new MySqlProvider();
            }
        }

        private static void BindParameters(FluentData.IDbCommand cmd, params DbField[] parameters)
        {
            foreach (var param in parameters)
            {
                cmd.Parameter(param.Name, param.Value);
            }
        }

        /// <summary>
        /// <para>added by Labbor on 20170728 执行一段SQL</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int Execute(string sql, params DbField[] parameters)
        {
            using (var tContext = Db.UseTransaction(true))
            {
                var cmd = tContext.Sql(sql).CommandType(DbCommandTypes.Text);
                BindParameters(cmd, parameters);
                var ret = cmd.Execute();
                tContext.Commit();
                return ret;
            }
        }

        /// <summary>
        /// <para>added by Labbor on 20170728 获取一个实体</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T GetModel<T>(string sql, params DbField[] parameters)
        {
            var cmd = Db.Sql(sql);
            BindParameters(cmd, parameters);
            return cmd.QuerySingle<T>();
        }

        /// <summary>
        /// <para>added by Labbor on 20170728 获取一个实体集</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> GetModels<T>(string sql, params DbField[] parameters)
        {
            var cmd = Db.Sql(sql);
            BindParameters(cmd, parameters);
            return cmd.QueryMany<T>();
        }

        public static int Insert(IDbBuilder dbb)
        {
            return Insert(dbb, null);
        }


        public static int Insert(IDbBuilder dbb, IDbContext db)
        {
            if (db == null)
                db = Db;
            var data = dbb.Data;

            // update dependency filed value
            if (data.Parent != null)
                data.AllFields.ForEach(x =>
                {
                    if (x.DependencyField != null)
                        x.Value = x.DependencyField.Value;
                });

            var ib = db.Insert(data.TableName);
            data.Fields.ForEach(x =>
            {
                if (x.IsId)
                    return;
                if (!string.IsNullOrEmpty(x.DbName) && !string.IsNullOrEmpty(x.Value + ""))
                    ib.Column(x.DbName, x.Value);
            });

            var idField = data.AllFields.FirstOrDefault(x => x.IsId);
            if (idField == null)
            {
                idField = data.Fields.FirstOrDefault(x => x.IsId);
            }

            if (idField != null)
            {
                // update id field for later using
                idField.Value = ib.ExecuteReturnLastId<string>();
                return 1;
            }
            else
            {
                return ib.Execute();
            }
        }



        public static int Delete(IDbBuilder dbb)
        {
            using (var db = Db.UseTransaction(true))
            {
                try
                {
                    var count = Delete(dbb, db);
                    db.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    db.Rollback();
                    throw ex;
                }
            }
        }

        public static int Delete(IDbBuilder dbb, IDbContext db)
        {
            if (db == null)
                db = Db;

            var data = dbb.Data;
            if (!data.DeleteAnyway)
            {
                return StatusDelete(dbb, db);
            }


            // delete children
            if (data.Setting != null && data.Setting.Children.Any())
            {
                data.Setting.Children.ForEach(c =>
                {
                    if (!c.Dependency.Fields.Any())
                        return;
                    var dependencyFields = new List<DbField>();
                    c.Dependency.Fields.ForEach(df =>
                    {
                        //dependencyFields.Add(new DbField { DbName = df.DbName, DependencyField = data.AllFields.FirstOrDefault(x => x.DbName == df.DependencyName) });

                        //var child = data.Children.FirstOrDefault(o => o.Data.TableName == c.Table);
                        var pdf = data.AllFields.FirstOrDefault(x => x.DbName == df.DependencyName);
                        if (pdf == null)
                        {
                            pdf = data.Fields.FirstOrDefault(x => x.DbName == df.DependencyName);
                        }
                        if (pdf == null)
                        {
                            pdf = data.Parent.Data.AllFields.FirstOrDefault(m => m.Name == df.DependencyName);
                        }
                        dependencyFields.Add(new DbField { DbName = df.DbName, DependencyField = pdf });
                    });
                    DeleteChildrenBySetting(db, c, dependencyFields);
                });
            }

            var buider = db.Delete(data.TableName);

            if (data.Wheres.Any())
            {
                foreach (var ws in data.Wheres)
                {
                    buider.AddWhere(string.Format(" {0} {1} = @{2} ", ws.LogicalKey.ToString(), ws.DbName, ws.ParamName));
                }
            }
            if (!string.IsNullOrEmpty(data.Where))
            {
                buider.AddWhere(data.Where);
            }

            data.Params.ForEach(x =>
            {
                buider.AddWhereParam(x.ParamName, x.Value);
            });
            return buider.Execute();
        }


        public static int StatusDelete(IDbBuilder dbb, IDbContext db)
        {
            var builder = db.Update(dbb.Data.TableName);
            var data = dbb.Data;
            //builder.Column("is_deleted", true);
            builder.Column("IsDeleted", true);
            if (data.Wheres.Any())
            {
                foreach (var ws in data.Wheres)
                {
                    builder.AddWhere(string.Format(" {0} {1} = @{2} ", ws.LogicalKey.ToString(), ws.DbName, ws.ParamName));
                }
            }
            if (!string.IsNullOrEmpty(data.Where))
            {
                builder.AddWhere(data.Where);
            }

            data.Params.ForEach(x =>
            {
                builder.AddWhereParam(x.ParamName, x.Value);
            });
            return builder.Execute();
        }


        public static int Update(IDbBuilder dbb)
        {
            return Update(dbb, null);
        }

        public static int Update(IDbBuilder dbb, IDbContext db)
        {
            var data = dbb.Data;
            if (!data.Fields.Any())
                return 0;
            if (string.IsNullOrEmpty(data.TableName))
                throw new Exception("Table cannot be empty or nothing");
            if (db == null)
                db = Db;
            var builder = db.Update(data.TableName);
            if (data.Wheres.Any())
            {
                data.Wheres.ForEach(x =>
                {
                    builder.AddWhere(string.Format(" {0} {1} = @{2}", x.LogicalKey, x.DbName, x.ParamName));
                });
            }
            if (!string.IsNullOrEmpty(data.Where))
            {
                builder.AddWhere(data.Where);
            }
            data.Fields.ForEach(x =>
            {
                if (x.IsId)
                    return;
                if (x.Value + "" == "")
                    x.Value = DBNull.Value;
                builder.Column(x.DbName, x.Value);
            });
            if (data.Params.Any())
            {
                data.Params.ForEach(x =>
                {
                    if (!builder.Data.Columns.Any(o => o.ParameterName == x.ParamName))
                        builder.AddWhereParam(x.ParamName, x.Value);
                });
            }
            return builder.Execute();
        }


        public static void DeleteChildrenBySetting(IDbContext db, SqlConfig setting, List<DbField> dependencyFields)
        {
            StringBuilder sqlSb = new StringBuilder();
            sqlSb.AppendFormat("Delete From {0} Where ", setting.Table);
            int idx = 0;
            var para = new List<DbField>();
            dependencyFields.ForEach(f =>
            {
                if (idx == 0)
                {
                    sqlSb.AppendFormat(" {0} = @__P{1}", f.DbName, ++idx);
                }
                else
                {
                    sqlSb.AppendFormat(" And {0} = @__P{1}", f.DbName, ++idx);
                }
                para.Add(new DbField { ParamName = "__p" + idx, Value = f.DependencyField.Value });
            });

            //delete it's children
            if (setting.Children.Any())
            {
                setting.Children.ForEach(cSetting =>
                {
                    //iterate it to get the dependency data 
                    StringBuilder sqlQuerySb = new StringBuilder();
                    sqlQuerySb.AppendFormat("Select * From {0} Where ", setting.Table);
                    int cIdx = 0;
                    var cPara = new List<DbField>();
                    dependencyFields.ForEach(f =>
                    {
                        if (cIdx == 0)
                        {
                            sqlQuerySb.AppendFormat(" {0} = @__P{1}", f.DbName, ++cIdx);
                        }
                        else
                        {
                            sqlQuerySb.AppendFormat(" And {0} = @__P{1}", f.DbName, ++cIdx);
                        }
                        cPara.Add(new DbField { ParamName = "__p" + cIdx, Value = f.DependencyField.Value });
                    });

                    var builderQuery = db.Sql(sqlQuerySb.ToString());
                    cPara.ForEach(x =>
                    {
                        builderQuery.Parameter(x.ParamName, x.Value);
                    });
                    // delete it's children
                    var table = builderQuery.QuerySingle<DataTable>();
                    if (table.Rows.Count > 0 && cSetting.Dependency.Fields.Any())
                    {
                        table.Rows.Cast<DataRow>().ToList().ForEach(r =>
                        {
                            var cDependencyFields = new List<DbField>();
                            cSetting.Dependency.Fields.ForEach(df =>
                            {
                                cDependencyFields.Add(new DbField { DbName = df.DbName, DependencyField = new DbField { DbName = df.DependencyName, Value = r[df.DependencyName] } });
                            });
                            // delete children
                            DeleteChildrenBySetting(db, cSetting, cDependencyFields);
                        });

                    }
                    //DeleteChildrenBySetting(cSetting);
                });
            }
            var builder = db.Sql(sqlSb.ToString());
            para.ForEach(p =>
            {
                builder.Parameter(p.ParamName, p.Value);
            });
            builder.Execute();
        }


    }
}
